import mysql.connector
from mysql.connector import Error
import pandas as pd
from typing import List, Dict, Optional
import getpass


class MySQLDatabase:
    """MySQL数据库操作类"""

    def __init__(self, host: str = 'localhost', user: str = None,
                 password: str = None, database: str = 'testdb'):
        """
        初始化数据库连接

        Args:
            host: 数据库主机地址
            user: 用户名
            password: 密码
            database: 数据库名
        """
        self.host = host
        self.user = user
        self.password = password
        self.database = database
        self.connection = None
        self.cursor = None

    def connect(self) -> bool:
        """
        连接到MySQL数据库

        Returns:
            bool: 连接是否成功
        """
        try:
            self.connection = mysql.connector.connect(
                host=self.host,
                user=self.user,
                password=self.password,
                database=self.database,
                charset='utf8mb4',
                collation='utf8mb4_unicode_ci'
            )

            if self.connection.is_connected():
                self.cursor = self.connection.cursor(dictionary=True)
                print(f"成功连接到数据库 '{self.database}'")
                return True

        except Error as e:
            print(f"数据库连接失败: {e}")
            return False

    def disconnect(self):
        """断开数据库连接"""
        if self.connection and self.connection.is_connected():
            if self.cursor:
                self.cursor.close()
            self.connection.close()
            print("数据库连接已关闭")

    def execute_query(self, query: str, params: tuple = None) -> Optional[List[Dict]]:
        """
        执行查询语句

        Args:
            query: SQL查询语句
            params: 查询参数

        Returns:
            查询结果列表或None
        """
        try:
            self.cursor.execute(query, params or ())
            results = self.cursor.fetchall()
            return results

        except Error as e:
            print(f"查询执行失败: {e}")
            return None

    def get_all_users(self) -> Optional[List[Dict]]:
        """
        获取users表的所有记录

        Returns:
            users表的所有记录或None
        """
        query = "SELECT * FROM users"
        return self.execute_query(query)

    def get_users_count(self) -> int:
        """获取users表的记录数量"""
        query = "SELECT COUNT(*) as count FROM users"
        result = self.execute_query(query)
        return result[0]['count'] if result else 0

    def get_table_structure(self, table_name: str = 'users') -> Optional[List[Dict]]:
        """
        获取表结构信息

        Args:
            table_name: 表名

        Returns:
            表结构信息或None
        """
        query = """
                SELECT COLUMN_NAME, \
                       DATA_TYPE, \
                       IS_NULLABLE, \
                       COLUMN_DEFAULT, \
                       COLUMN_KEY, \
                       EXTRA
                FROM INFORMATION_SCHEMA.COLUMNS
                WHERE TABLE_SCHEMA = %s \
                  AND TABLE_NAME = %s
                ORDER BY ORDINAL_POSITION \
                """
        return self.execute_query(query, (self.database, table_name))

    def create_sample_users_table(self):
        """创建示例users表（如果不存在）"""
        create_table_query = """
                             CREATE TABLE IF NOT EXISTS users \
                             ( \
                                 id \
                                 INT \
                                 AUTO_INCREMENT \
                                 PRIMARY \
                                 KEY, \
                                 name \
                                 VARCHAR \
                             ( \
                                 100 \
                             ) NOT NULL,
                                 email VARCHAR \
                             ( \
                                 255 \
                             ) UNIQUE NOT NULL,
                                 age INT,
                                 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                                 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                                 ) \
                             """

        insert_sample_data_query = """
                                   INSERT \
                                   IGNORE INTO users (name, email, age) VALUES
        ('张三', 'zhangsan@example.com', 25),
        ('李四', 'lisi@example.com', 30),
        ('王五', 'wangwu@example.com', 28),
        ('赵六', 'zhaoliu@example.com', 35) \
                                   """

        try:
            # 创建表
            self.cursor.execute(create_table_query)

            # 插入示例数据
            self.cursor.execute(insert_sample_data_query)
            self.connection.commit()

            print("示例users表创建完成并插入了测试数据")

        except Error as e:
            print(f"创建示例表失败: {e}")
            self.connection.rollback()


def display_results(results: List[Dict], title: str = "查询结果"):
    """美观地显示查询结果"""
    if not results:
        print("没有找到任何记录")
        return

    print(f"\n{'=' * 60}")
    print(f"{title}")
    print(f"{'=' * 60}")

    # 使用pandas DataFrame来美化显示
    df = pd.DataFrame(results)
    print(df.to_string(index=False))

    print(f"\n总计: {len(results)} 条记录")


def display_table_structure(structure: List[Dict]):
    """显示表结构"""
    if not structure:
        print("无法获取表结构")
        return

    print(f"\n{'=' * 60}")
    print("users表结构")
    print(f"{'=' * 60}")

    df = pd.DataFrame(structure)
    print(df.to_string(index=False))


def get_connection_details() -> tuple:
    """获取用户输入的连接详情"""
    print("请输入MySQL数据库连接信息")
    print("(直接按Enter使用默认值)")

    host = input("主机地址 [localhost]: ").strip() or 'localhost'
    user = input("用户名 [root]: ").strip() or 'root'
    password = getpass.getpass("密码: ")
    database = input("数据库名 [testdb]: ").strip() or 'testdb'

    return host, user, password, database


def main():
    """主函数"""
    print("MySQL数据库查询工具")
    print("=" * 50)

    # 获取连接信息
    host, user, password, database = get_connection_details()

    # 创建数据库实例
    db = MySQLDatabase(host, user, password, database)

    try:
        # 连接数据库
        if not db.connect():
            return

        # 检查users表是否存在，如果不存在则创建示例表
        table_structure = db.get_table_structure('users')
        if not table_structure:
            print("users表不存在，是否创建示例表？ (y/n)")
            choice = input().strip().lower()
            if choice == 'y':
                db.create_sample_users_table()
                table_structure = db.get_table_structure('users')
            else:
                print("操作取消")
                return

        # 显示表结构
        display_table_structure(table_structure)

        # 获取记录数量
        count = db.get_users_count()
        print(f"\nusers表共有 {count} 条记录")

        # 获取所有用户记录
        users = db.get_all_users()

        if users:
            display_results(users, "users表所有记录")
        else:
            print("无法获取用户记录")

        # 可选：执行自定义查询
        print(f"\n{'=' * 50}")
        print("提示: 您可以执行自定义查询")
        custom_query = input("输入自定义SQL查询 (直接按Enter跳过): ").strip()

        if custom_query:
            if custom_query.lower().startswith('select'):
                results = db.execute_query(custom_query)
                if results is not None:
                    display_results(results, "自定义查询结果")
            else:
                print("只支持SELECT查询")

    except KeyboardInterrupt:
        print("\n\n程序被用户中断")
    except Exception as e:
        print(f"\n发生错误: {e}")
    finally:
        # 确保断开连接
        db.disconnect()


def simple_example():
    """简单使用示例"""
    print("简单连接示例")

    # 简单连接方式
    try:
        connection = mysql.connector.connect(
            host='localhost',
            user='root',
            password='your_password',  # 替换为你的密码
            database='testdb'
        )

        if connection.is_connected():
            cursor = connection.cursor(dictionary=True)

            # 查询users表
            cursor.execute("SELECT * FROM users")
            results = cursor.fetchall()

            print("users表记录:")
            for row in results:
                print(row)

            cursor.close()
            connection.close()

    except Error as e:
        print(f"连接失败: {e}")


if __name__ == "__main__":
    # 运行主程序
    simple_example()